package DAO;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import model.FeedBack;

public class FeedBackDao {

	public FeedBackDao() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	String url = "jdbc:mysql://localhost:3306/QUESTUDY";
	String user = "root";
	String pass = "webclass";

	// 특정 answer에 대한 feedback
	/*public ArrayList<FeedBack> getAllFeedBackInfo(int answerId) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<FeedBack> feedBackList = new ArrayList<FeedBack>();
		String targetMember = null;
		try {
			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select fBackId, feedback.qid, aid, feedback.content, memid, feedback.createdTime, feedback.updatedTime from feedback, answer,user,study where answerId=aid and feedback.uid=userid and aid=? order by fBackId desc");
			pstmt.setInt(1, answerId);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				pstmt = con
						.prepareStatement("select memid from user,feedback where targetId=userid and aid=?");
				pstmt.setInt(1, answerId);
				rs = pstmt.executeQuery();
				if (rs.next()) {
					targetMember = rs.getString("memid").trim();
				}
				int fBackId = rs.getInt("fBackId");
				int qid = rs.getInt("qid");
				int aid = rs.getInt("aid");
				String content = rs.getString("content").trim();
				String memid = rs.getString("memid");
				Timestamp createdTime = rs.getTimestamp("createdTime");
				Timestamp updatedTime = rs.getTimestamp("updatedTime");
				feedBackList.add(new FeedBack(fBackId, qid, aid, content,
						memid, targetMember, createdTime, updatedTime));
			}

			return feedBackList;

		} catch (Exception ex) {
			ex.printStackTrace();
			return feedBackList;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}	*/
	public ArrayList<FeedBack> getAllFeedBackInfo(int answerId) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<FeedBack> feedBackList = new ArrayList<FeedBack>();
		String targetMember = null;
		try {
			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select fBackId, feedback.qid, aid, feedback.content, memid, feedback.createdTime, feedback.updatedTime from feedback, user where aid=? and userid=uid order by fBackId desc");
			pstmt.setInt(1, answerId);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				
				int fBackId = rs.getInt("fBackId");
				int qid = rs.getInt("qid");
				int aid = rs.getInt("aid");
				String content = rs.getString("content").trim();
				String memid = rs.getString("memid");
				Timestamp createdTime = rs.getTimestamp("createdTime");
				Timestamp updatedTime = rs.getTimestamp("updatedTime");
				feedBackList.add(new FeedBack(fBackId, qid, aid, content,
						memid, targetMember, createdTime, updatedTime));
			}

			return feedBackList;

		} catch (Exception ex) {
			ex.printStackTrace();
			return feedBackList;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}


	// feedback 작성
	public void insertFeedBackInfo(FeedBack newFeedBack) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("insert into feedback(qid, aid, content, uid, targetId, createdTime) values(?,?,?,?,?,sysdate())");
			pstmt.setInt(1, newFeedBack.getQid());
			pstmt.setInt(2, newFeedBack.getAid());
			pstmt.setString(3, newFeedBack.getContent());
			pstmt.setInt(4, newFeedBack.getUid());
			pstmt.setInt(5, newFeedBack.getTargetId());

			pstmt.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();

		} finally {
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// feedback 작성
	public void updateFeedBackInfo(FeedBack updateFeedBack) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("update feedback set content=?, updatedTime=sysdate() where fBackId=?");
			pstmt.setString(1, updateFeedBack.getContent());
			pstmt.setInt(2, updateFeedBack.getfBackId());

			pstmt.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();

		} finally {
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

}